import logging

import mysql.connector
from typing import List, Dict, Optional, Union
from mysql.connector import Error


class MySQLDatabaseTool:
    """
    MySQL数据库操作工具类
    提供数据库和数据表的完整DDL操作功能
    """

    def __init__(self, host: str = 'localhost', port: int = 3306,
                 user: str = 'root', password: str = '123456',
                 database: Optional[str] = None):
        """
        初始化数据库连接参数

        :param host: 数据库主机地址
        :param port: 数据库端口
        :param user: 数据库用户名
        :param password: 数据库密码
        :param database: 数据库名称
        """
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        self.cursor = None

    def connect(self) -> bool:
        """
        建立数据库连接

        :return: 连接是否成功
        """
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database
            )
            self.cursor = self.connection.cursor(dictionary=True)
            return True
        except Error as e:
            print(f"数据库连接失败: {e}")
            return False

    def close(self):
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.connection and self.connection.is_connected():
            self.connection.close()

    # ==================== 数据库操作 ====================

    def create_database(self, db_name: str, charset: str = 'utf8mb4') -> bool:
        """
        创建数据库

        :param db_name: 数据库名称
        :param charset: 字符集
        :return: 操作是否成功
        """
        try:
            if not self.connection or not self.connection.is_connected():
                if not self.connect():
                    return False

            sql = f"CREATE DATABASE IF NOT EXISTS `{db_name}` CHARACTER SET {charset}"
            self.cursor.execute(sql)
            self.connection.commit()
            return True
        except Error as e:
            print(f"创建数据库失败: {e}")
            return False

    def show_databases(self) -> Optional[List[str]]:
        """
        查看所有数据库

        :return: 数据库名称列表
        """
        try:
            if not self.connection or not self.connection.is_connected():
                if not self.connect():
                    return None

            self.cursor.execute("SHOW DATABASES")
            result = self.cursor.fetchall()
            return [db['Database'] for db in result]
        except Error as e:
            print(f"查看数据库列表失败: {e}")
            return None

    def use_database(self, db_name: str) -> bool:
        """
        切换数据库

        :param db_name: 数据库名称
        :return: 操作是否成功
        """
        try:
            if not self.connection or not self.connection.is_connected():
                if not self.connect():
                    return False

            self.cursor.execute(f"USE `{db_name}`")
            self.database = db_name
            return True
        except Error as e:
            print(f"切换数据库失败: {e}")
            return False

    def drop_database(self, db_name: str) -> bool:
        """
        删除数据库

        :param db_name: 数据库名称
        :return: 操作是否成功
        """
        try:
            if not self.connection or not self.connection.is_connected():
                if not self.connect():
                    return False

            sql = f"DROP DATABASE IF EXISTS `{db_name}`"
            self.cursor.execute(sql)
            self.connection.commit()
            return True
        except Error as e:
            print(f"删除数据库失败: {e}")
            return False

    # ==================== 数据表操作 ====================

    def create_table(self, table_name: str, columns: List[Dict]) -> bool:
        """
        创建数据表

        :param table_name: 表名
        :param columns: 字段定义列表
        :return: 操作是否成功
        """
        try:
            if not self.connection or not self.connection.is_connected():
                if not self.connect():
                    return False

            column_definitions = []
            primary_keys = []

            for col in columns:
                col_def = [f"`{col['name']}` {col['type']}"]

                # 添加约束
                if col.get('not_null'):
                    col_def.append("NOT NULL")
                if col.get('unique'):
                    col_def.append("UNIQUE")
                if 'default' in col:
                    default_val = col['default']
                    if isinstance(default_val, (int, float)):
                        col_def.append(f"DEFAULT {default_val}")
                    else:
                        col_def.append(f"DEFAULT '{default_val}'")
                if col.get('auto_increment'):
                    col_def.append("AUTO_INCREMENT")
                if col.get('primary_key'):
                    primary_keys.append(f"`{col['name']}`")

                column_definitions.append(" ".join(col_def))

            # 添加主键约束
            if primary_keys:
                column_definitions.append(f"PRIMARY KEY ({', '.join(primary_keys)})")

            sql = f"CREATE TABLE IF NOT EXISTS `{table_name}` ({', '.join(column_definitions)})"
            self.cursor.execute(sql)
            self.connection.commit()
            return True
        except Error as e:
            print(f"创建表失败: {e}")
            return False

    def describe_table(self, table_name: str) -> Optional[List[Dict]]:
        """
        查看表结构

        :param table_name: 表名
        :return: 表结构信息
        """
        try:
            if not self.connection or not self.connection.is_connected():
                if not self.connect():
                    return None

            self.cursor.execute(f"DESCRIBE `{table_name}`")
            return self.cursor.fetchall()
        except Error as e:
            print(f"查看表结构失败: {e}")
            return None

    def drop_table(self, table_name: str) -> bool:
        """
        删除表

        :param table_name: 表名
        :return: 操作是否成功
        """
        try:
            if not self.connection or not self.connection.is_connected():
                if not self.connect():
                    return False

            sql = f"DROP TABLE IF EXISTS `{table_name}`"
            self.cursor.execute(sql)
            self.connection.commit()
            return True
        except Error as e:
            print(f"删除表失败: {e}")
            return False

    # ==================== 表结构修改操作 ====================

    def add_column(self, table_name: str, column: Dict) -> bool:
        """
        新增字段

        :param table_name: 表名
        :param column: 字段定义
        :return: 操作是否成功
        """
        try:
            if not self.connection or not self.connection.is_connected():
                if not self.connect():
                    return False

            col_def = [f"`{column['name']}` {column['type']}"]

            if column.get('not_null'):
                col_def.append("NOT NULL")
            if column.get('unique'):
                col_def.append("UNIQUE")
            if 'default' in column:
                default_val = column['default']
                if isinstance(default_val, (int, float)):
                    col_def.append(f"DEFAULT {default_val}")
                else:
                    col_def.append(f"DEFAULT '{default_val}'")

            sql = f"ALTER TABLE `{table_name}` ADD COLUMN {' '.join(col_def)}"
            self.cursor.execute(sql)
            self.connection.commit()
            return True
        except Error as e:
            print(f"新增字段失败: {e}")
            return False

    def modify_column(self, table_name: str, column: Dict) -> bool:
        """
        修改字段

        :param table_name: 表名
        :param column: 字段定义
        :return: 操作是否成功
        """
        try:
            if not self.connection or not self.connection.is_connected():
                if not self.connect():
                    return False

            col_def = [f"`{column['name']}` {column['type']}"]

            if column.get('not_null'):
                col_def.append("NOT NULL")
            if column.get('unique'):
                col_def.append("UNIQUE")
            if 'default' in column:
                default_val = column['default']
                if isinstance(default_val, (int, float)):
                    col_def.append(f"DEFAULT {default_val}")
                else:
                    col_def.append(f"DEFAULT '{default_val}'")

            sql = f"ALTER TABLE `{table_name}` MODIFY COLUMN {' '.join(col_def)}"
            self.cursor.execute(sql)
            self.connection.commit()
            return True
        except Error as e:
            print(f"修改字段失败: {e}")
            return False

    def drop_column(self, table_name: str, column_name: str) -> bool:
        """
        删除字段

        :param table_name: 表名
        :param column_name: 字段名
        :return: 操作是否成功
        """
        try:
            if not self.connection or not self.connection.is_connected():
                if not self.connect():
                    return False

            sql = f"ALTER TABLE `{table_name}` DROP COLUMN `{column_name}`"
            self.cursor.execute(sql)
            self.connection.commit()
            return True
        except Error as e:
            print(f"删除字段失败: {e}")
            return False
